//
//  DataHandle.m
//  NewMyMusicAPP
//
//  Created by dlios on 15/6/28.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataHandle.h"

@implementation DataHandle

+(instancetype)shareHandleController
{
    //创建单例对象
    static DataHandle *data = nil;
    
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        
        data = [[DataHandle alloc] init];
        
        [data network];
    });
    
    return data;
}

-(void)network
{
    self.monitorNetwork = NO;
}


    static sqlite3 *db = nil;

-(void)openDB
{
    if (db != nil) {
        return;
    }
    //获取路径
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    NSString *MusicPath = [filePath stringByAppendingPathComponent:@"UserPath.sqlite"];
    NSLog(@"MusicPath = %@",MusicPath);
    
    
    int result = sqlite3_open(MusicPath.UTF8String, &db);
    if (result == SQLITE_OK) {
 //       NSLog(@"打开数据成功");
    }else
    {
        NSLog(@"打开数据失败");
    }
    
}

#pragma mark -- 官方推荐
//创建官方推荐表
- (void)createOfficialTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS Official (number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description_label TEXT, posterPic TEXT, modelId TEXT, type TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
   //     NSLog(@"创建官方推荐表成功");
    }else
    {
        NSLog(@"创建官方推荐表失败");
    }
 
}

//插入官方推荐数据
- (void)insertOfficial:(OfficialModel *)official
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Official (title, description_label, posterPic, modelId, type) VALUES('%@', '%@','%@', '%@', '%@')",official.title,official.description_label,official.posterPic,official.modelId,official.type];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
   //     NSLog(@"插入官方推荐数据成功");
    }else
    {
        NSLog(@"插入官方推荐数据失败");
    }
}

//查询官方推荐数据
- (NSMutableArray *)selectOfficial
{
    NSString *sql = @"SELECT * FROM Official";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *description_label = sqlite3_column_text(stmt, 2);
            const unsigned char *posterPic = sqlite3_column_text(stmt, 3);
            const unsigned char *modelId = sqlite3_column_text(stmt, 4);
            const unsigned char *type = sqlite3_column_text(stmt, 5);
            OfficialModel *official = [[OfficialModel alloc] init];
            official.title = [NSString stringWithUTF8String:(const char *) title];
            official.description_label = [NSString stringWithUTF8String:(const char *) description_label];
            official.posterPic = [NSString stringWithUTF8String:(const char *) posterPic];
            official.modelId = [NSString stringWithUTF8String:(const char *) modelId];
            official.type = [NSString stringWithUTF8String:(const char *) type];
            [array addObject:official];
            
        }
        
  //      NSLog(@"查询官方数据成功");
        sqlite3_finalize(stmt); //释放stmt 清除内存
        
        return array;
    }else
    {
        NSLog(@"查询官方数据失败");
        return nil;
    }
    
    
}

//删除官方数据表
- (void)dropOfficialTable
{
    NSString *sql = @"DROP TABLE Official";
    int  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
   //     NSLog(@"删除官方数据表成功");
    }else
    {
        NSLog(@"删除官方数据表失败");
    }
    
}

#pragma mark -- MV首播

//创建MV首播表
- (void)createPremiereTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS Premiere (number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, artistName TEXT, albumImg TEXT, posterPic TEXT, idd TEXT, type TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
  //      NSLog(@"创建MV首播表成功");
    }else
    {
        NSLog(@"创建MV首播表失败");
    }
    

}

//插入MV首播数据
- (void)insertPremiere:(MVpreimereModel *)preimere
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Premiere (title, artistName, albumImg, posterPic, idd, type) VALUES('%@', '%@','%@','%@','%@','%@')",preimere.title,preimere.artistName,preimere.albumImg,preimere.posterPic,preimere.idd,preimere.type];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
  //      NSLog(@"插入MV首播数据成功");
    }else
    {
        NSLog(@"插入MV首播数据失败");
    }

}

//查询MV首播数据
- (NSMutableArray *)selectPremiere
{
    NSString *sql = @"SELECT * FROM Premiere";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *artistName = sqlite3_column_text(stmt, 2);
            const unsigned char *albumImg = sqlite3_column_text(stmt, 3);
            const unsigned char *posterPic = sqlite3_column_text(stmt, 4);
            const unsigned char *idd = sqlite3_column_text(stmt, 5);
            const unsigned char *type = sqlite3_column_text(stmt, 6);

            
            
            MVpreimereModel *preimere = [[MVpreimereModel alloc] init];
            preimere.title = [NSString stringWithUTF8String:(const char *) title];
            preimere.artistName = [NSString stringWithUTF8String:(const char *) artistName];
            preimere.albumImg = [NSString stringWithUTF8String:(const char *) albumImg];
            preimere.posterPic = [NSString stringWithUTF8String:(const char *) posterPic];
            preimere.idd =[NSString stringWithUTF8String:(const char *) idd];
            preimere.type=[NSString stringWithUTF8String:(const char *) type];

            
            [array addObject:preimere];
            
        }
        
       // NSLog(@"查询MV首播数据成功");
        sqlite3_finalize(stmt); //释放stmt 清除内存
        
        return array;
    }else
    {
        NSLog(@"查询MV首播数据失败");
        return nil;
    }
    
    

}

//删除MV首播表
- (void)dropPremiereTable
{
    NSString *sql = @"DROP TABLE Premiere";
    int  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
     //   NSLog(@"删除MV首播数据表成功");
    }else
    {
        NSLog(@"删除MV首播数据表失败");
    }
    
}

#pragma mark --正在流行
//创建正在流行表
- (void)createPopularTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS Popular (number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, artistName TEXT, albumImg TEXT, posterPic TEXT, idd TEXT, type TEXT )";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if ( result == SQLITE_OK) {
    //    NSLog(@"创建正流行表成功");
    }else{
        NSLog(@"创建正在流行表失败");
    }
    
}
//插入正在流行表
- (void)insertPopular:(PopularModel *)popularM
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Popular (title, artistName, albumImg, posterPic, idd, type) VALUES('%@', '%@','%@','%@','%@','%@')",popularM.title,popularM.artistName,popularM.albumImg,popularM.posterPic,popularM.idd,popularM.type];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
    //    NSLog(@"插入正在流行数据成功");
    }else{
        NSLog(@"插入正在流行数据失败");
    }
    
}
//查询正在流行表
-(NSMutableArray *)selectPopular
{
    NSString *sql = @"SELECT *FROM Popular";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result ==SQLITE_OK) {
      //  NSLog(@"准备查询正在流行");
        
        while (sqlite3_step(stmt)== SQLITE_ROW) {
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *artistName = sqlite3_column_text(stmt, 2);
            const unsigned char *albumImg = sqlite3_column_text(stmt, 3);
            const unsigned char *posterPic = sqlite3_column_text(stmt, 4);
            const unsigned char *idd = sqlite3_column_text(stmt, 5);
            const unsigned char *type = sqlite3_column_text(stmt, 6);

            
            PopularModel *popularM=[[PopularModel alloc]init];
            popularM.title=[NSString stringWithUTF8String:(const char *)title ];
            popularM.artistName=[NSString stringWithUTF8String:(const char *)artistName];
            popularM.albumImg = [NSString stringWithUTF8String:(const char *)albumImg];
            popularM.posterPic=[NSString stringWithUTF8String:(const char *)posterPic];
            popularM.idd=[NSString stringWithUTF8String:(const char *)idd];
            popularM.type=[NSString stringWithUTF8String:(const char *)type];
            
            [array addObject:popularM];
            
        }
        
        return array;
    }else
    {
        //释放stmt,清除内存
        sqlite3_finalize(stmt);
        
        NSLog(@"准备查询正在流行失败");
        return nil;
        
    }

}

//删除正在流行表
-(void)dropPopularTable
{
    NSString *sql = @"DROP TABLE Popular";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
    //    NSLog(@"删除正在流行表成功");
    }else
    {
        NSLog(@"删除正在流行表失败");
    }
    
}

#pragma mark -- MV收藏
//创建收藏表
- (void)createCollectTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS Collect (number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, modelId TEXT, type TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
    //    NSLog(@"创建收藏表成功");
    }else
    {
        NSLog(@"创建收藏表失败");
    }
    

}

//插入收藏数据
- (void)insertCollect:(CollectModel *)collect
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Collect (title, modelId, type) VALUES('%@', '%@','%@')",collect.title,collect.modelId,collect.type];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
       // NSLog(@"插入收藏数据成功");
    }else
    {
        NSLog(@"插入收藏数据失败");
    }

}
//查询收藏数据
- (NSMutableArray *)selectCollect
{
    NSString *sql = @"SELECT * FROM Collect";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *modelId = sqlite3_column_text(stmt, 2);
            const unsigned char *type = sqlite3_column_text(stmt, 3);
            CollectModel *collect = [[CollectModel alloc] init];
            collect.title = [NSString stringWithUTF8String:(const char *) title];
            collect.modelId = [NSString stringWithUTF8String:(const char *) modelId];
            collect.type = [NSString stringWithUTF8String:(const char *) type];
            
            
            [array addObject:collect];
            
        }
        
    //    NSLog(@"查询收藏数据成功");
        sqlite3_finalize(stmt); //释放stmt 清除内存
        
        return array;
    }else
    {
        NSLog(@"查询收藏数据失败");
        return nil;
    }
    
    
}

//删除收藏某个数据
-(void)deleteTable:(NSString *)modelId
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM Collect WHERE modelId = '%@'", modelId];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
      //  NSLog(@"删除收藏某个数据成功");
    }else
    {
        NSLog(@"删除收藏某个数据失败");
    }
    
    
}
- (void)dropCollectTable
{
    NSString *sql = @"DROP TABLE Collect";
    int  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
  //      NSLog(@"删除官方数据表成功");
    }else
    {
        NSLog(@"删除官方数据表失败");
    }
    
}


#pragma mark -- 音乐FM
//创建音乐FM缓存表
- (void)createMusicFMTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS MusicFM (number INTEGER PRIMARY KEY AUTOINCREMENT, tname TEXT, cover_path TEXT )";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
  //      NSLog(@"创建音乐FM缓存表成功");
    }else
    {
        NSLog(@"创建音乐FM缓存表失败");
    }
    
}

//插入音乐FM缓存数据
- (void)insertMusicFM:(MusicFMModel *)MusicFM
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO MusicFM (tname, cover_path) VALUES('%@', '%@')",MusicFM.tname,MusicFM.cover_path];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
  //      NSLog(@"插入音乐FM缓存数据成功");
    }else
    {
        NSLog(@"插入音乐FM缓存数据失败");
    }
}

//查询音乐FM缓存数据
- (NSMutableArray *)selectMusicFM
{
    NSString *sql = @"SELECT * FROM MusicFM";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *tname = sqlite3_column_text(stmt, 1);
            const unsigned char *cover_path = sqlite3_column_text(stmt, 2);
          
            
            MusicFMModel *musicFM = [[MusicFMModel alloc] init];
            musicFM.tname = [NSString stringWithUTF8String:(const char *) tname];
            musicFM.cover_path = [NSString stringWithUTF8String:(const char *) cover_path];
    
            
            [array addObject:musicFM];
            
        }
        
    //    NSLog(@"查询音乐FM缓存成功");
        sqlite3_finalize(stmt); //释放stmt 清除内存
        
        return array;
    }else
    {
        NSLog(@"查询音乐FM缓存数据失败");
        return nil;
    }
    
    
}



//删除音乐FM缓存表
- (void)dropMusicFMTable
{
    NSString *sql = @"DROP TABLE MusicFM";
    int  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
   //     NSLog(@"删除音乐FM缓存数据表成功");
    }else
    {
        NSLog(@"删除音乐FM缓存数据表失败");
    }
    
}

#pragma mark -- 音乐FM轮播图
//创建音乐FM轮播图缓存表
- (void)createMusicFMPicTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS MusicFMPic (number INTEGER PRIMARY KEY AUTOINCREMENT, Pic TEXT )";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
 //       NSLog(@"创建音乐FM轮播图缓存表成功");
    }else
    {
        NSLog(@"创建音乐FM轮播图缓存表失败");
    }
    
}

//插入音乐FM缓存数据
- (void)insertMusicFMPic:(MusicFMModel *)MusicFM
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO MusicFMPic (Pic) VALUES('%@')",MusicFM.pic];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
  //      NSLog(@"插入音乐FM轮播图缓存数据成功");
    }else
    {
        NSLog(@"插入音乐FM缓存数据失败");
    }
}

//查询音乐FM缓存数据
- (NSMutableArray *)selectMusicFMPic
{
    NSString *sql = @"SELECT * FROM MusicFMPic";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *pic = sqlite3_column_text(stmt, 1);
            
            
            
            MusicFMModel *musicFM = [[MusicFMModel alloc] init];
            musicFM.pic = [NSString stringWithUTF8String:(const char *)pic];
            
            
            
            [array addObject:musicFM];
            
        }
        
  //      NSLog(@"查询音乐FM轮播图缓存成功");
        sqlite3_finalize(stmt); //释放stmt 清除内存
        
        return array;
    }else
    {
        NSLog(@"查询音乐FM缓存数据失败");
        return nil;
    }
    
    
}



//删除音乐FM缓存表
- (void)dropMusicFMPicTable
{
    NSString *sql = @"DROP TABLE MusicFMPic";
    int  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
 //       NSLog(@"删除音乐FM缓存数据表成功");
    }else
    {
        NSLog(@"删除音乐FM缓存数据表失败");
    }
    
}



#pragma mark -- 音乐FM收藏
//创建音乐FM收藏表
- (void)createMusicFMCollection
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS MusicFMCollection (number INTEGER PRIMARY KEY AUTOINCREMENT, trackId TEXT,title TEXT,playUrl32 TEXT, albumTitle TEXT,coverLarge TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
    //    NSLog(@"创建音乐FM收藏表成功");
    }else
    {
        NSLog(@"创建音乐FM收藏表失败");
    }
    
}

//插入音乐FM收藏数据
- (void)insertMusicFMCollection:(FMPersonModel *)MusicFMPlaye
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO MusicFMCollection (trackId , title , playUrl32, albumTitle, coverLarge) VALUES('%@','%@','%@','%@','%@')",MusicFMPlaye.trackId , MusicFMPlaye.title,MusicFMPlaye.playUrl32,MusicFMPlaye.albumTitle,MusicFMPlaye.coverLarge];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
    //    NSLog(@"插入音乐FM收藏数据成功");
    }else
    {
        NSLog(@"插入音乐FM收藏数据失败");
    }
}

//查询音乐FM收藏数据
- (NSMutableArray *)selectMusicFMCollection
{
    NSString *sql = @"SELECT * FROM MusicFMCollection";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *trackId = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            const unsigned char *playUrl32 = sqlite3_column_text(stmt, 3);
            const unsigned char *albumTitle = sqlite3_column_text(stmt, 4);
            const unsigned char *coverLarge = sqlite3_column_text(stmt, 5);
            
            
            FMPersonModel *musicFM = [[FMPersonModel alloc] init];
            musicFM.trackId = [NSString stringWithUTF8String:(const char *)trackId];
            musicFM.title = [NSString stringWithUTF8String:(const char *)title];
            musicFM.playUrl32 = [NSString stringWithUTF8String:(const char *)playUrl32];
            musicFM.albumTitle = [NSString stringWithUTF8String:(const char *)albumTitle];
            musicFM.coverLarge = [NSString stringWithUTF8String:(const char *)coverLarge];
            
            [array addObject:musicFM];
            
        }
        
 //       NSLog(@"查询音乐FM收藏成功");
        sqlite3_finalize(stmt); //释放stmt 清除内存
        
        return array;
    }else
    {
        NSLog(@"查询音乐FM收藏数据失败");
        return nil;
    }
    
    
}



//删除音乐FM收藏表
- (void)dropMusicFMCollectionTable
{
    NSString *sql = @"DROP TABLE MusicFMCollection";
    int  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
   //     NSLog(@"删除音乐FM收藏数据表成功");
    }else
    {
        NSLog(@"删除音乐FM收藏数据表失败");
    }
    
}

//删除单条收藏
-(void)deleteMusicFMCollection:(NSString *)title
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM MusicFMCollection WHERE title = '%@'",title];
    
    int result = sqlite3_exec(db, sql.UTF8String, NULL,NULL, nil);
    if (result == SQLITE_OK) {
  //      NSLog(@"单条删除成功");
    }else{
        NSLog(@"单条删除失败");
    }
    
    
}






@end
